﻿<?php

function gentime() {
    static $a;
    if($a == 0) $a = microtime(true);
    else return (string)(microtime(true)-$a);
}
gentime();

include("class.base.php");
if ($sess = sisse_logitud()) { //sisse logitud
	if (!$minu_grupi_oigused = grupi_oigused($sess)) {
		$minu_grupi_oigused = 0;
	}
	if ($minu_grupi_oigused == 2) {
		$tabelid = getSearchableTables();
		$excludes = excludes();
	}
	elseif ($minu_grupi_oigused == 3) {
		$tabelid = getSearchableTables();
		$excludes = excludes(0);
	}
	else {
		$tabelid = getPublicTables();
		$excludes = excludes();
	}
	$where = "";
	$where2 = "";
	
	$minuLemmikud = mysql_query("SELECT * FROM lemmikud WHERE lisaja=".$sess["id"]);
	$lemmikud = array();
	while ($lemmik = mysql_fetch_array($minuLemmikud)) {
		array_push($lemmikud, $lemmik["tabel"]."-".$lemmik["vali"]);
	}
	$jsLemmik = "";
	$minuLemmikudLink = "seaded.php";
}
else { //välja logitud
	$minu_grupi_oigused = 0;
	$tabelid = getPublicTables();
	if (isset($_SESSION["lemmikud"])) { $lemmikud = $_SESSION["lemmikud"]; }
	else { $lemmikud = array(); }
	$jsLemmik = "_a";
	$minuLemmikudLink = "minu_lemmikud.php";
	$where = " oigused='avalik' AND ";
	$where2 = " WHERE oigused='avalik'";
	$excludes = excludes();
}

$nonSystemTables = getNonSystemTables();
$publicTables = getPublicTables();

$orig_aeg = array(
"audio_trakk" => "salvestusaeg",
"video_trakk" => "salvestusaeg",
"foto" => "pildistamisaeg",
"kasikiri" => "koostamise_aeg");

$orderRollidBy = "ORDER BY FIELD(rolli_nimetus";
$rolli_nimetus = mysql_query("SELECT id FROM rolli_nimetus ORDER BY jrknr DESC");
while ($rolli_nimi = mysql_fetch_array($rolli_nimetus)) {
	$orderRollidBy .= ",'".$rolli_nimi["id"]."'";
}
$orderRollidBy .= ") DESC";

if (isset($_GET["otsi_andmebaasist"])) {
	$_SESSION["otsing"] = $_SERVER["REQUEST_URI"];
	$pealkiri1 = $lang["otsi"];
	$mida = htmlspecialchars($_GET["mida"], ENT_QUOTES);
	mysql_query("INSERT INTO otsingu_logi SET ip='".$_SERVER["REMOTE_ADDR"]."', sona='".$mida."', tabel='lihtotsing', grupp='".$minu_grupi_oigused."', andmebaasi_lisatud=NOW()");
	$mida = str_replace("*", "%", $mida);
	$equals = $_GET["equals"];
	if ($equals == "jah") {
		$checked_box = "checked";
	}
	else {
		$checked_box = "";
	}
	if ($mida == "") { $viga = $lang["tyhi_otsing"]; }
	else {
		$sisu =<<<SISU
<form action="" method="GET">
	<table width="100%">
	<tr><td><a href="$minuLemmikudLink">$lang[minu_lemmikud]</a></td></tr>
	<tr><td>
	$lang[otsi_andmebaasist]<br />
	$lang[tapne_vaste] <input type="checkbox" name="equals" value="jah" $checked_box /><br />
	<input type="text" name="mida" value="$_GET[mida]"> <input type="submit" name="otsi_andmebaasist" value="$lang[otsi]">
	</td></tr>
	<tr><td>$lang[otsi_paremalt]</td></tr>
	</table>
</form>
SISU;
		$kestusKokku = 0;
		$kestusKokku2 = 0;
		$lehekylgiKokku = 0;
		$finalResults = array();
		$rowCounts = array();
		$collect = explode(" ", $mida);
		if ($equals != "jah") {
			for ($i = 0; $i < count($collect); $i++) {
				$collect[$i] = "%".$collect[$i]."%";
			}
		}
		else {
			for ($i = 0; $i < count($collect); $i++) {
				$collect[$i] = $collect[$i];
			}
		}
		foreach ($tabelid as $tabel) {
			if (in_array($tabel, $publicTables)) {
				$whereIn = "";
				$sqlPlus = "";
				$veerud = get_columns("osaleja", $excludes);
				for ($i = 0; $i < count($collect); $i++) {
					$whereIn2 = "";
					$query = mysql_query("SELECT id FROM kihelkond WHERE kihelkonna_nimi LIKE '".$collect[$i]."'");
					while ($row = mysql_fetch_array($query)) {
						$whereIn2 .= "'".$row["id"]."',";
					}
					$sqlPlus .= "(";
					foreach ($veerud as $veerg) {
						$sqlPlus .= $veerg." LIKE '".$collect[$i]."' OR ";
						if ($veerg == "kihelkond" && $whereIn2) {
							$whereIn2 = rtrim($whereIn2, ",");
							$sqlPlus .= $veerg." IN (".$whereIn2.") OR ";
						}
					}
					$sqlPlus = rtrim($sqlPlus, " OR ");
					$sqlPlus .= ") AND ";
				}
				$sqlPlus = rtrim($sqlPlus, " AND ");
				if ($otsi = mysql_query("SELECT materjal_id FROM roll WHERE materjal='".$tabel."' AND osaleja IN (SELECT id FROM osaleja WHERE ".$sqlPlus.")")) {
					while ($rida = mysql_fetch_array($otsi)) {
						$whereIn .= $rida["materjal_id"].",";
					}
				}
			}
			$sqlPlus = "";
			$veerud = get_columns($tabel, $excludes);
			for ($i = 0; $i < count($collect); $i++) {
				$sqlPlus .= "(";
				foreach ($veerud as $veerg) {
					if ($veerg != "keel") {
						$sqlPlus .= $veerg." LIKE '".$collect[$i]."' OR ";
					}
				}
				$sqlPlus = rtrim($sqlPlus, " OR ");
				$sqlPlus .= ") AND ";
			}
			$sqlPlus = rtrim($sqlPlus, " AND ");
			if (in_array("keel", $veerud)) {
				$c = 0;
				$sqlKeel = "";
				foreach ($collect as $mida) {
					if ($c == 0) { $sqlKeel .= "keel LIKE '".$mida."'"; $c++; }
					else { $sqlKeel .= " OR keel LIKE '".$mida."'"; }
				}
				$otsi = mysql_query("SELECT materjal_id FROM materjal_keel WHERE materjal='".$tabel."' AND (".$sqlKeel.")");
				while ($rida = mysql_fetch_array($otsi)) {
					$whereIn .= $rida["materjal_id"].",";
				}
			}
			if ($whereIn) {
				$whereIn = rtrim($whereIn, ",");
				$sqlPlus .= " OR id IN (".$whereIn.")";
			}
			$sql = "SELECT id FROM ".$tabel." WHERE ".$where."(".$sqlPlus.") ORDER BY id DESC";
			if ($otsi = mysql_query($sql)) {
				while ($rida = mysql_fetch_array($otsi)) {
					$finalResults[] = array($rida["id"], $tabel);
				}
				$rowCounts[$tabel] = mysql_num_rows($otsi);
			}
			unset($whereIn);
		}
		//teeme kokkuvõtted ära ja loeme üle mitu vastust leiti
		$whereIn = "";
		for ($i = 0; isset($finalResults[$i]); $i++) {
			if (!isset($currentTable)) {
				$currentTable = $finalResults[$i][1];
			}
			if ($currentTable == "audio_trakk" || $currentTable == "video_trakk" || $currentTable == "kasikiri") {
				$whereIn .= $finalResults[$i][0].",";
			}
			if (!isset($finalResults[$i+1]) || $finalResults[$i+1][1] != $currentTable) {
				if ($whereIn) {
					$whereIn = rtrim($whereIn, ",");
					if ($currentTable == "audio_trakk") {
						$paring = mysql_query("SELECT SUM(kestus) AS kestus_kokku FROM ".$currentTable." WHERE id IN (".$whereIn.")");
						if ($paring && $rida = mysql_fetch_array($paring)) {
							$kestusKokku = $kestusKokku + $rida["kestus_kokku"];
						}
					}
					elseif ($currentTable == "video_trakk") {
						$paring = mysql_query("SELECT SUM(kestus) AS kestus_kokku FROM ".$currentTable." WHERE id IN (".$whereIn.")");
						if ($paring && $rida = mysql_fetch_array($paring)) {
							$kestusKokku2 = $kestusKokku2 + $rida["kestus_kokku"];
						}
					}
					elseif ($currentTable == "kasikiri") {
						$paring = mysql_query("SELECT SUM(lehekylgede_arv) AS lehekylgede_arv_kokku FROM ".$currentTable." WHERE id IN (".$whereIn.")");
						if ($paring && $rida = mysql_fetch_array($paring)) {
							$lehekylgiKokku = $lehekylgiKokku + $rida["lehekylgede_arv_kokku"];
						}
					}
				}
				unset($currentTable);
				$whereIn = "";
			}
		}
		$noOfResults = count($finalResults);
		if ($noOfResults == 0) {
			$viga = $lang["midagi_ei_leitud"];
		}
		else {			
			$teade = $lang["leiti"]." " . $noOfResults . " ".$lang["vastust"];
			foreach ($rowCounts as $tabel => $rowCount) {
				if ($rowCount) {
					$teade .= "<br />".kriips($tabel).": ".$rowCount." ".$lang["rida"];
				}
				if ($tabel == "audio_trakk" && $kestusKokku > 0) {
					$teade .= "<br />".$lang["trakkide_kogupikkus_on"]." ".pretty_length($kestusKokku);
				}
				if ($tabel == "video_trakk" && $kestusKokku2 > 0) {
					$teade .= "<br />".$lang["video_trakkide_kogupikkus_on"]." ".pretty_length($kestusKokku2);
				}
				if ($tabel == "kasikiri" && $lehekylgiKokku > 0) {
					$teade .= "<br />".$lang["lehekylgi_kokku_on"]." ".$lehekylgiKokku;
				}
			}
			
			$riduLehel = 25; //ühel lehel näidatakse nii mitu rida
			if (isset($_GET["l"])) { $leheNr = $_GET["l"]; settype($leheNr, 'int'); }
			else { $leheNr = 0; }
			$naitaAlates = $leheNr*$riduLehel; //arvuta mitmendast reast näitama hakatakse
			if ($noOfResults > $riduLehel) {
				if (isset($_GET["l"])) {
					$otsinguParing = explode("&l=", $_SERVER["REQUEST_URI"]);
					$sisu .= pages(rawurldecode($otsinguParing[0]).'&l=%d', $leheNr, $noOfResults, $riduLehel);
				}
				else {
					$sisu .= pages(rawurldecode($_SERVER["REQUEST_URI"]).'&l=%d', $leheNr, $noOfResults, $riduLehel);
				}
			}
			//kuvame lehekülje jagu tulemusi
			$rowsToShow = array();
			$naitaKuni = $naitaAlates + $riduLehel;
			unset($currentTable);
			for ($whereIn = ""; $naitaAlates < $naitaKuni; $naitaAlates++) {
				if (!isset($finalResults[$naitaAlates])) { break; }
				if (!isset($currentTable)) {
					$currentTable = $finalResults[$naitaAlates][1];
				}
				$whereIn .= $finalResults[$naitaAlates][0].",";
				if ($naitaAlates == $naitaKuni-1 || $finalResults[$naitaAlates+1][1] != $currentTable) {
					if ($whereIn) {
						$whereIn = rtrim($whereIn, ",");
						$rowsToShow[$currentTable] = mysql_query("SELECT * FROM ".$currentTable." WHERE id IN (".$whereIn.")");
					}
					unset($currentTable);
					$whereIn = "";
				}
			}
			foreach ($rowsToShow as $tabel => $read) {
				$must_show_columns = get_must_show_columns($tabel);
				if (in_array($tabel, $publicTables)) {
					array_push($must_show_columns, "roll");
				}
				$sisu .= "<table class='colspace'>";
				$sisu .= "<tr><th><h2>".kriips($tabel)."</h2></th></tr>";
				$sisu .= "<tr>";
				$sisu .= "<td></td>";
				foreach ($must_show_columns as $value) {
					$sisu .= "<td>".kriips($value)."</td>";
				}
				if ($tabel == "osaleja") {
					$sisu .= "<td>".$lang["arhiivimaterjal"]."</td>";
				}
				$sisu .= "</tr>";
				while ($rida = mysql_fetch_array($read)) {
					$resource_name = $tabel."-".$rida["id"];
					$sisu .= "<tr>";
					$sisu .= "<td style='white-space: nowrap;'>";
					if ($minu_grupi_oigused > 2 or $rida["lisaja"] == $sess["id"]) {
						$sisu .= "
						<a href='muuda.php?t=".$tabel."&id=".$rida["id"]."'><img src='kujundus/pildid/muuda.png' title='".$lang["muuda"]."' alt='".$lang["muuda"]."' /></a> 
						<a href='kustuta.php?t=".$tabel."&id=".$rida["id"]."'><img src='kujundus/pildid/kustuta.png' title='".$lang["kustuta"]."' alt='".$lang["kustuta"]."' /></a> ";
					}
					$sisu .= "
						<span id='".$resource_name."'>";
					if (in_array($resource_name, $lemmikud)) {
						$sisu .= "<img src='kujundus/pildid/ok.png' alt='".$lang["sinu_lemmik"]."' title='".$lang["sinu_lemmik"]."'>";
					}
					else {
						$sisu .= "<img src='kujundus/pildid/lemmik.png' title='".$lang["lisa_lemmikutesse"]."' alt='".$lang["lisa_lemmikutesse"]."' onclick=\"lisa_lemmik".$jsLemmik."('".$tabel."', '".$rida["id"]."'); this.style.display='none'; \" />";
					}
					$sisu .= "</span>";
					if (isset($rida["digitaalselt_olemas"]) && $rida["digitaalselt_olemas"] == 1) {
						$sisu .= " <img src='kujundus/pildid/digi.png' alt='".kriips("digitaalselt_olemas")."' title='".kriips("digitaalselt_olemas")."' />";
					}
					elseif (isset($rida["fyysiliselt_olemas"]) && $rida["fyysiliselt_olemas"] == 1) {
						$sisu .= " <img src='kujundus/pildid/fyysiline.png' alt='".kriips("fyysiliselt_olemas")."' title='".kriips("fyysiliselt_olemas")."' />";
					}
					elseif (isset($rida["ressursi_tyyp"]) && ($rida["ressursi_tyyp"] == 1 && $rida["fyysiliselt_olemas"] == 2 || $rida["ressursi_tyyp"] == 2 && $rida["digitaalselt_olemas"] == 2)) {
						$sisu .= " <img src='kujundus/pildid/kadunud.png' alt='".$lang["pole_saadaval"]."' title='".$lang["pole_saadaval"]."' />";
					}
					$sisu .= "</td>";
					foreach ($must_show_columns as $value) {
						$sisu .= "<td>";
						if ($minu_grupi_oigused > 0 or $tabel != "osaleja") {
							$sisu .= "<a href=\"naita.php?t=".$tabel."&id=".$rida["id"]."\">";
						}
						if ($value == "kestus") {
							$sisu .= " ".pretty_length($rida[$value]);
						}
						elseif ($value == "materjal") {
							if ($query = mysql_query("SELECT * FROM ".$rida["materjal"]." WHERE id='".$rida["materjal_id"]."'")) {
								$sisu .= kriips($rida["materjal"]);
								$row = mysql_fetch_array($query);
								$must_show_columns2 = get_must_show_columns($rida["materjal"]);
								foreach ($must_show_columns2 as $value2) {
									if ($value2 == "kestus") {
										$sisu .= " ".pretty_length($row[$value2]);
									}
									else {
										$sisu .= " ".$row[$value2];
									}
								}
							}
						}
						elseif ($value == "roll") {
							if ($query = mysql_query("SELECT * FROM ".$value." WHERE materjal='".$tabel."' AND materjal_id='".$rida["id"]."' ".$orderRollidBy)) {
								$must_show_columns2 = get_must_show_columns($value);
								$must_show_columns3 = get_must_show_columns("osaleja");
								while ($row = mysql_fetch_array($query)) {
									$osalejaParing = mysql_fetch_array(mysql_query("SELECT * FROM osaleja WHERE id='".$row["osaleja"]."'"));
									foreach ($must_show_columns2 as $value2) {
										if ($value2 == "vanus" && ($row["vanus"] || $row["vanus"] = vanus($osalejaParing["synniaasta"], $rida[$orig_aeg[$tabel]]))) {
											$sisu .= " (".$row[$value2].")";
										}
										elseif ($value2 == "materjal" or $value2 == "materjal_id") {}
										elseif ($value2 == "osaleja") {
											foreach ($must_show_columns3 as $value3) {
												$sisu .= " ".$osalejaParing[$value3];
											}
										}
										else {
											$sisu .= " ".$row[$value2];
										}
									}
									$sisu .= "<br />";
								}
							}
						}
						elseif (in_array($value, $nonSystemTables)) {
							if ($query = mysql_query("SELECT * FROM ".$value." WHERE id='".$rida[$value]."'")) {
								$row = mysql_fetch_array($query);
								$must_show_columns2 = get_must_show_columns($value);
								foreach ($must_show_columns2 as $value2) {
									$sisu .= " ".$row[$value2];
								}
							}
						}
						else {
							$sisu .= korrasta($rida[$value], 40);
						}
						$sisu .= "</a></td>";
					}
					if ($tabel == "osaleja") {
						$sisu .= "<td>";
						$rollidParing = mysql_query("SELECT * FROM roll WHERE osaleja=".$rida["id"]);
						while ($rollidRida = mysql_fetch_array($rollidParing)) {
							$sisu .= kriips($rollidRida["materjal"]).": ";
							if ($teineParing = mysql_query("SELECT * FROM ".$rollidRida["materjal"]." WHERE id='".$rollidRida["materjal_id"]."'")) {
								$teineRida = mysql_fetch_array($teineParing);
								$must_show_columns2 = get_must_show_columns($rollidRida["materjal"]);
								$sisu .= "<a href='naita.php?t=".$rollidRida["materjal"]."&id=".$rollidRida["materjal_id"]."'>";
								foreach ($must_show_columns2 as $value2) {
									if ($value2 == "kestus") {
										$sisu .= " ".pretty_length($teineRida[$value2]);
									}
									else {
										$sisu .= " ".$teineRida[$value2];
									}
								}
								$sisu .= "</a>";
							}
							$sisu .= "<br />";
						}
						$sisu .= "</td>";
					}
					$sisu .= "</tr>";
				}
				$sisu .= "<tr><td><br /></td></tr>";
				$sisu .= "</table>";
			}
		}
	}
}
elseif (isset($_GET["tabel"])) { //tahetkse saada otsimise vormi
	$xtabel = htmlspecialchars($_GET["tabel"], ENT_QUOTES);
	if (!in_array($xtabel, $tabelid)) { $viga = $lang["tabelit_ei_leitud"]; }
	else {
		$sisu = "";
		$columns1 = get_full_columns($xtabel, $excludes);
		if (in_array($xtabel, $publicTables)) {
			if ($xtabel != "kasikiri") {
				array_unshift($columns1, array("sari"));
			}
			$columns2 = get_full_columns("osaleja", $excludes);
		}
		if (isset($_GET[$xtabel])) { //tahetakse otsida
			if (in_array($xtabel, $publicTables)) {
				$veeruNimed = array_merge($columns1, $columns2);
			}
			else {
				$veeruNimed = $columns1;
			}
			$_SESSION["otsing"] = $_SERVER["REQUEST_URI"];
			$r = $_GET;
			$count1 = 0;
			$count2 = 0;
			$sql1 = ""; // päring peatabelisse
			$sql2 = ""; // päring osalejate tabelisse
			$searchString = "";
			$whereIn = "";
			foreach ($veeruNimed as $columns) {
				if (in_array($columns, $columns1)) {
					$sql = $sql1;
					$count = $count1;
				}
				else {
					$sql = $sql2;
					$count = $count2;
				}
				$n = htmlspecialchars($r[$columns[0]], ENT_QUOTES); //muuda ära erisümbolid
				$tyyp = $columns[1]; //otsitava andme tüüp
				$tyyp = explode("(", $tyyp);
				$tyyp = $tyyp[0]; //int, varchar, date, datetime etc
				if ($tyyp != "date" and (strstr($columns[0], "aeg") or strstr($columns[0], "aasta"))) {
					$n22 = $columns[0]."2";
					$n2 = $r[$n22];
					if ($n2 != "" and $n == "") {
						$n = "0";
					}
				}
				if ($tyyp != "date" and !in_array($columns[0], $excludes) and ($r[$columns[0]."_equals"] == "checked" || $n != "")) { //eemaldame päringust mittevajaliku
					if ($count > 0) { $sql .= " AND "; }
					if ($tyyp == "int") {
						$n33 = $columns[0]."_equals";
						$n3 = $r[$n33];
						if ($n3 == "checked") {
							$sql .= $columns[0]." LIKE '".$n."'";
							$searchString .= $columns[0]."=".$n.", ";
						}
						else {
							$sql .= $columns[0].">=".$n;
							$searchString .= $columns[0].">=".$n.", ";
							$n22 = $columns[0]."2";
							$n2 = htmlspecialchars($r[$n22], ENT_QUOTES);
							if ($n2 != "") {
								$sql .= " AND ".$columns[0]."<=".$n2;
								$searchString .= $columns[0]."<=".$n.", ";
								$count++;
							}
						}
					}
					else {
						$n = str_replace("*", "%", $n);
						$n33 = $columns[0]."_equals";
						$n3 = $r[$n33];
						if (strstr($columns[0], "aeg") or strstr($columns[0], "aasta")) {
							$n22 = $columns[0]."2";
							$n2 = htmlspecialchars($r[$n22], ENT_QUOTES);
							if ($n3 == "checked") {
								$sql .= "SUBSTR(".$columns[0].", -4) LIKE '".$n."'";
								$searchString .= $columns[0]."=".$n.", ";
							}
							else {
								$sql .= "SUBSTR(".$columns[0].", -4)>=".$n;
								$searchString .= $columns[0].">=".$n.", ";
								if ($n2 != "") {
									$sql .= " AND SUBSTR(".$columns[0].", -4)<=".$n2;
									$searchString .= $columns[0]."<=".$n.", ";
									$count++;
								}
							}
						}
						elseif ($columns[0] == "keel") {
							if ($n3 == "checked") { $n = "%".$n."%"; }
							$query = mysql_query("SELECT materjal_id FROM materjal_keel WHERE materjal='".$xtabel."' AND keel LIKE '".$n."'");
							while ($row = mysql_fetch_array($query)) {
								$whereIn .= $row["materjal_id"].",";
							}
							if ($whereIn) {
								$whereIn = rtrim($whereIn, ",");
								$sql .= "id IN (".$whereIn.")";
							}
							$searchString .= $columns[0]."=".$n.", ";
						}
						elseif ($columns[0] == "sari" && in_array($xtabel, $publicTables) && $xtabel != "kasikiri") {
							if ($xtabel == "audio_trakk") { $field = "audio"; }
							elseif ($xtabel == "video_trakk") { $field = "video"; }
							elseif ($xtabel == "foto") { $field = "fotoalbum"; }
							else { continue; }
							$sql .= $field." IN (SELECT id FROM ".$field." WHERE sari LIKE '".$n."')";
							$searchString .= $columns[0]."=".$n.", ";
						}
						elseif ($n3 == "checked") {
							$sql .= $columns[0]." LIKE '".$n."'";
							$searchString .= $columns[0]."=".$n.", ";
						}
						else {
							$sql .= $columns[0]." LIKE '%".$n."%'";
							$searchString .= $columns[0]."=*".$n."*, ";
						}
					}
					$count++;
				}
				if ($tyyp == "date") {
					$n33 = $columns[0]."_equals";
					$n3 = $r[$n33];
					if ($n3 == "checked") {
						if ($count > 0) { $sql .= " AND "; }
						$sql .= $columns[0]." LIKE '".date_to_mysql($n)."'";
						$searchString .= $columns[0]."=".$n.", ";
						$count++;
					}
					else {
						if ($n != "" and $kuup = date_to_mysql($n)) {
							if ($count > 0) { $sql .= " AND "; }
							$sql .= $columns[0].">='".$kuup."'";
							$searchString .= $columns[0].">=".$n.", ";
							$count++;
						}
						$n22 = $columns[0]."2";
						$n2 = htmlspecialchars($r[$n22], ENT_QUOTES);
						if ($n2 != "" and $kuup = date_to_mysql($n2)) {
							if ($count > 0) { $sql .= " AND "; }
							$sql .= $columns[0]."<='".$kuup."'";
							$searchString .= $columns[0]."<=".$n.", ";
							$count++;
						}
					}
				}
				if (in_array($columns, $columns1)) {
					$sql1 = $sql;
					$count1 = $count;
				}
				else {
					$sql2 = $sql;
					$count2 = $count;
				}
			}
			$count = $count1 + $count2;
			
			$must_show_columns = get_must_show_columns($xtabel);
			if (in_array($xtabel, $publicTables)) {
				array_push($must_show_columns, "roll");
			}
			$rolli_nimetus = htmlspecialchars($r["rolli_nimetus"], ENT_QUOTES);
			if ($rolli_nimetus) {
				if ($r["rolli_nimetus_equals"] == "checked") {
					$sql3 = " AND rolli_nimetus LIKE '".$rolli_nimetus."'";
					$searchString .= "rolli_nimetus=".$rolli_nimetus.", ";
				}
				else {
					$sql3 = " AND rolli_nimetus LIKE '%".$rolli_nimetus."%'";
					$searchString .= "rolli_nimetus=*".$rolli_nimetus."*, ";
				}
				$count++;
			}
			else { $sql3 = ""; }
			
			$kestusKokku = 0;
			$lehekylgiKokku = 0;
			$korduvateKontroll = array();
			$whereIn = "";
			if ($sql2 || $sql3) {
				if ($sql2) { $sql2 = " AND osaleja IN (SELECT id FROM osaleja WHERE (".$sql2."))"; }
				$paring3 = mysql_query("SELECT materjal_id FROM roll WHERE materjal='".$xtabel."'".$sql3.$sql2);
				while ($kokkuvotteRida3 = mysql_fetch_array($paring3)) {
					$whereIn .= $kokkuvotteRida3["materjal_id"].",";
				}
			}
			if ($sql1 || $sql2 && $whereIn || $sql3 && $whereIn) {
				if ($whereIn) {
					$whereIn = rtrim($whereIn, ",");
					if ($sql1) {
						$sql1 .= " AND ";
					}
					$sql1 .= "id IN (".$whereIn.")";
				}
				if ($xtabel == "audio_trakk" or $xtabel == "video_trakk") {
					$paring = mysql_query("SELECT SUM(kestus) AS kestus_summa FROM ".$xtabel." WHERE ".$where."(".$sql1.")") or $viga = mysql_error();
					$paring = mysql_fetch_array($paring);
					$kestusKokku = $paring["kestus_summa"];
				}
				elseif ($xtabel == "kasikiri") {
					$paring = mysql_query("SELECT SUM(lehekylgede_arv) AS lehekylgede_arv_summa FROM ".$xtabel." WHERE ".$where."(".$sql1.")") or $viga = mysql_error();
					$paring = mysql_fetch_array($paring);
					$lehekylgiKokku = $paring["lehekylgede_arv_summa"];
				}
				if ($searchString) {
					$searchString = rtrim($searchString, ", ");
					mysql_query("INSERT INTO otsingu_logi SET ip='".$_SERVER["REMOTE_ADDR"]."', sona='".$searchString."', tabel='".$xtabel."', grupp='".$minu_grupi_oigused."', andmebaasi_lisatud=NOW()");
				}
				$paring = mysql_query("SELECT id FROM ".$xtabel." WHERE ".$where."(".$sql1.")") or $viga = mysql_error();
				while ($rida = mysql_fetch_array($paring)) {
					array_push($korduvateKontroll, $rida["id"]);
				}
			}
			$riduLehel = 25; //ühel lehel näidatakse nii mitu rida
			if (isset($_GET["l"])) { $leheNr = $_GET["l"]; settype($leheNr, 'int'); }
			else { $leheNr = 0; }
			$naitaAlates = $leheNr*$riduLehel; //arvuta mitmendast reast näitama hakatakse
			$naitaRidu = count($korduvateKontroll);
			if ($naitaRidu > $riduLehel) {
				if (isset($_GET["l"])) {
					$otsinguParing = explode("&l=", $_SERVER["REQUEST_URI"]);
					$lehed = pages(rawurldecode($otsinguParing[0]).'&l=%d', $leheNr, $naitaRidu, $riduLehel);
				}
				else {
					$lehed = pages(rawurldecode($_SERVER["REQUEST_URI"]).'&l=%d', $leheNr, $naitaRidu, $riduLehel);
				}
			}
			else { $lehed = ""; }
			if ($naitaRidu > 0) {
				rsort($korduvateKontroll);
				$sisu .= $lehed."<table class=\"colspace\"><tr><th></th>";
				foreach ($must_show_columns as $value) {
					$sisu .= "<th>".kriips($value)."</th>";
				}
				$sisu .= "</tr>";
				$naitaKuni = $naitaAlates + $riduLehel;
				for ($whereIn = ""; $naitaAlates < $naitaKuni; $naitaAlates++) {
					$whereIn .= $korduvateKontroll[$naitaAlates].",";
				}
				$whereIn = rtrim($whereIn, ",");
				$paring = mysql_query("SELECT * FROM ".$xtabel." WHERE id IN (".$whereIn.")");
				while ($rida = mysql_fetch_array($paring)) {
					$resource_name = $xtabel."-".$rida["id"];
					$sisu .= "<tr><td style='white-space: nowrap;'>";
					if ($minu_grupi_oigused > 1) {
						$sisu .= "
						<a href='muuda.php?t=".$xtabel."&id=".$rida["id"]."'><img src='kujundus/pildid/muuda.png' title='".$lang["muuda"]."' alt='".$lang["muuda"]."' /></a> 
						<a href='kustuta.php?t=".$xtabel."&id=".$rida["id"]."'><img src='kujundus/pildid/kustuta.png' title='".$lang["kustuta"]."' alt='".$lang["kustuta"]."' /></a> ";
					}
					$sisu .= "
						<span id='".$resource_name."'>";
					if (in_array($resource_name, $lemmikud)) {
						$sisu .= "<img src='kujundus/pildid/ok.png' alt='".$lang["sinu_lemmik"]."' title='".$lang["sinu_lemmik"]."' />";
					}
					else {
						$sisu .= "<img src='kujundus/pildid/lemmik.png' title='".$lang["lisa_lemmikutesse"]."' alt='".$lang["lisa_lemmikutesse"]."' onclick=\"lisa_lemmik".$jsLemmik."('".$xtabel."', '".$rida["id"]."'); this.style.display='none'; \" />";
					}
					$sisu .= "</span>";
					if (isset($rida["digitaalselt_olemas"]) && $rida["digitaalselt_olemas"] == 1) {
						$sisu .= " <img src='kujundus/pildid/digi.png' alt='".kriips("digitaalselt_olemas")."' title='".kriips("digitaalselt_olemas")."' />";
					}
					elseif (isset($rida["fyysiliselt_olemas"]) && $rida["fyysiliselt_olemas"] == 1) {
						$sisu .= " <img src='kujundus/pildid/fyysiline.png' alt='".kriips("fyysiliselt_olemas")."' title='".kriips("fyysiliselt_olemas")."' />";
					}
					elseif (isset($rida["ressursi_tyyp"]) && ($rida["ressursi_tyyp"] == 1 && $rida["fyysiliselt_olemas"] == 2 || $rida["ressursi_tyyp"] == 2 && $rida["digitaalselt_olemas"] == 2)) {
						$sisu .= " <img src='kujundus/pildid/kadunud.png' alt='Pole saadaval' title='Pole saadaval' />";
					}
					$sisu .= "</td>";
					foreach ($must_show_columns as $value) {
						$sisu .= "<td><a href=\"naita.php?t=".$xtabel."&id=".$rida["id"]."\">";
						if ($value == "kestus") {
							$sisu .= " ".pretty_length($rida[$value]);
						}
						elseif ($value == "materjal") {
							if ($query = mysql_query("SELECT * FROM ".$rida["materjal"]." WHERE id=".$rida["materjal_id"])) {
								$sisu .= kriips($rida["materjal"]);
								$row = mysql_fetch_array($query);
								$must_show_columns2 = get_must_show_columns($rida["materjal"]);
								foreach ($must_show_columns2 as $value2) {
									if ($value2 == "kestus") {
										$sisu .= " ".pretty_length($row[$value2]);
									}
									else {
										$sisu .= " ".$row[$value2];
									}
								}
							}
						}
						elseif ($value == "roll") {
							if ($query = mysql_query("SELECT * FROM ".$value." WHERE materjal='".$xtabel."' AND materjal_id=".$rida["id"])) {
								$must_show_columns2 = get_must_show_columns($value);
								$must_show_columns3 = get_must_show_columns("osaleja");
								while ($row = mysql_fetch_array($query)) {
									$osalejaParing = mysql_fetch_array(mysql_query("SELECT * FROM osaleja WHERE id=".$row["osaleja"]));
									foreach ($must_show_columns2 as $value2) {
										if ($value2 == "vanus" && ($row["vanus"] || $row["vanus"] = vanus($osalejaParing["synniaasta"], $rida[$orig_aeg[$xtabel]]))) {
											$sisu .= " (".$row[$value2].")";
										}
										elseif ($value2 == "materjal" or $value2 == "materjal_id") {}
										elseif ($value2 == "osaleja") {
											foreach ($must_show_columns3 as $value3) {
												$sisu .= " ".$osalejaParing[$value3];
											}
										}
										else {
											$sisu .= " ".$row[$value2];
										}
									}
									$sisu .= "<br />";
								}
							}
						}
						elseif (in_array($value, $nonSystemTables)) {
							if ($query = mysql_query("SELECT * FROM ".$value." WHERE id=".$rida[$value])) {
								$row = mysql_fetch_array($query);
								$must_show_columns2 = get_must_show_columns($value);
								foreach ($must_show_columns2 as $value2) {
									$sisu .= " ".$row[$value2];
								}
							}
						}
						else {
							$sisu .= " ".korrasta($rida[$value]);
						}
						$sisu .= "</a></td>";
					}
					$sisu .= "</tr>";
				}
				$sisu .= "</table>";
			}
			$teade = $lang["otsiti"]." ".$count." ".$lang["parameetri_jargi_leiti"]." ".$naitaRidu." ".$lang["rida"].".";
			if ($kestusKokku > 0) {
				$teade .= "<br />".$lang["trakkide_kogupikkus_on"]." ".pretty_length($kestusKokku);
			}
			elseif ($lehekylgiKokku > 0) {
				$teade .= "<br />".$lang["lehekylgi_kokku_on"]." ".$lehekylgiKokku;
			}
		} //taheti otsida
	
		$xtabel1 = kriips($xtabel, 0);
$pealkiri1 = $lang["otsi"]." ".$xtabel1;
		$sisu .=<<<SISU
<form action="" method="GET">
<table width="100%">
<tr><td><a href="$minuLemmikudLink">$lang[minu_lemmikud]</a></td></tr>
<tr><th>$lang[valja_nimi]</th><th>$lang[tapne_vaste]</th><th>$lang[otsitavad_vaartused]</th></tr>
SISU;
		function genereeriOtsimiseVorm($veeruNimed) {
			global $sisu;
			global $nonSystemTables;
			global $excludes;
			global $r;
			global $lang;
			global $xtabel;
			global $publicTables;
			foreach ($veeruNimed as $columns) { //genereeritakse html vorm
				if (!in_array($columns[0], $excludes)) {
				$n = htmlspecialchars($r[$columns[0]], ENT_QUOTES); //eemalda whitespace'd ja muuda ära erisümbolid
				$leiatyyp = explode("(", $columns[1]);
				$tyyp = $leiatyyp[0];
				$n3 = $r[$columns[0]."_equals"];
				$sisu .= "<tr><td>".kriips($columns[0])."</td><td>";
				if (in_array($columns[0], $nonSystemTables)) {
					$must_show_columns = get_must_show_columns($columns[0]);
					$kysi_teisest_tabelist = mysql_query("SELECT * FROM ".$columns[0]." ORDER BY ".$must_show_columns[0]);
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td>
						<select name='".$columns[0]."' style='width: 300px;' onchange='if (!this.value) { this.form.".$columns[0]."_equals.checked=false; } else { this.form.".$columns[0]."_equals.checked=true; }'>
							<option value=''></option>";
					while ($rida = mysql_fetch_array($kysi_teisest_tabelist)) {
						if ($columns[0] != "sari" || $rida["materjali_tyyp"] == $xtabel || $rida["materjali_tyyp"] == $xtabel."_trakk" || $rida["materjali_tyyp"]."album" == $xtabel) {
							if ($n == $rida["id"]) { $selected = " selected='selected'"; } else { $selected = ""; }
							$sisu .= "
								<option value='".$rida["id"]."'".$selected.">";
							foreach ($must_show_columns as $value) {
								$sisu .= $rida[$value]." ";
							}
							$sisu .= "</option>";
						}
					}
					$sisu .= "</select>";
				}
				elseif ($columns[0] == "ressursi_tyyp") {
					$n22 = "selected_ressursi_tyyp".$n;
					$$n22 = " selected='selected'";
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td>
					<select name='ressursi_tyyp' onchange='if (!this.value) { this.form.".$columns[0]."_equals.checked=false; } else { this.form.".$columns[0]."_equals.checked=true; }'>
						<option value=''></option>
						<option value='1'".$selected_ressursi_tyyp1.">".$lang["fyysiline"]."</option>
						<option value='2'".$selected_ressursi_tyyp2.">".$lang["digitaalne"]."</option>
					</select>";
				}
				elseif ($columns[0] == "fyysiliselt_olemas") {
					$n22 = "selected_fyysiliselt_olemas".$n;
					$$n22 = " selected='selected'";
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td>
					<select name='fyysiliselt_olemas' onchange='if (!this.value) { this.form.".$columns[0]."_equals.checked=false; } else { this.form.".$columns[0]."_equals.checked=true; }'>
						<option value=''></option>
						<option value='1'".$selected_fyysiliselt_olemas1.">".$lang["olemas"]."</option>
						<option value='2'".$selected_fyysiliselt_olemas2.">".$lang["kadunud"]."</option>
						<option value='3'".$selected_fyysiliselt_olemas3.">".$lang["laenatud"]."</option>
					</select>";
				}
				elseif ($columns[0] == "digitaalselt_olemas") {
					$n22 = "selected_digitaalselt_olemas".$n;
					$$n22 = " selected='selected'";
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td>
					<select name='digitaalselt_olemas' onchange='if (!this.value) { this.form.".$columns[0]."_equals.checked=false; } else { this.form.".$columns[0]."_equals.checked=true; }'>
						<option value=''></option>
						<option value='1'".$selected_digitaalselt_olemas1.">".$lang["jah"]."</option>
						<option value='2'".$selected_digitaalselt_olemas2.">".$lang["ei"]."</option>
					</select>";
				}
				elseif ($columns[0] == "kestus") {
					$n22 = $columns[0]."2";
					$n2 = $r[$n22];
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td><input type='text' name='".$columns[0]."' value='".$n."'> kuni <input type='text' name='".$n22."' value='".$n2."'> ".$lang["sekundid"];
				}
				elseif ($columns[0] == "oigused") {
					$n22 = "selected_oigused".$n;
					$$n22 = " selected='selected'";
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td>
						<select name='".$columns[0]."' onchange='if (!this.value) { this.form.".$columns[0]."_equals.checked=false; } else { this.form.".$columns[0]."_equals.checked=true; }'>
							<option value=''></option>
							<option value='avalik'".$selected_oigusedavalik.">".$lang["avalik"]."</option>
							<option value='mitteavalik'".$selected_oigusedmitteavalik.">".$lang["mitteavalik"]."</option>
						</select>";
				}
				elseif ($tyyp == "text") { $sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td><textarea name='".$columns[0]."' cols='25' rows='3'>".$n."</textarea>"; }
				elseif ($tyyp == "int") {
					$n22 = $columns[0]."2";
					$n2 = $r[$n22];
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td><input type='text' name='".$columns[0]."' value='".$n."'> ".$lang["kuni"]." <input type='text' name='".$n22."' value='".$n2."'>".pretty_filetype($tyyp);
				}
				elseif ($tyyp == "date") {
					$n22 = $columns[0]."2";
					$n2 = $r[$n22];
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td><input type='text' name='".$columns[0]."' value='".$n."'> ".$lang["kuni"]." <input type='text' name='".$n22."' value='".$n2."'>".pretty_filetype($tyyp);
				}
				elseif (strstr($columns[0], "aeg") or strstr($columns[0], "aasta")) {
					$n22 = $columns[0]."2";
					$n2 = $r[$n22];
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td><input type='text' name='".$columns[0]."' value='".$n."'> ".$lang["kuni"]." <input type='text' name='".$n22."' value='".$n2."'> ".$lang["aasta"]." ".$lang["AAAA"];
				}
				elseif ($columns[0] == "materjal") {
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td>
						<select name='".$columns[0]."' onchange='if (!this.value) { this.form.".$columns[0]."_equals.checked=false; } else { this.form.".$columns[0]."_equals.checked=true; }'>
							<option value=''></option>";
						foreach ($publicTables as $value) {
							if ($value == $n) {
								$selected = " selected='selected'";
							}
							else {
								$selected = "";
							}
							$sisu .= "
								<option value='".$value."'".$selected.">".kriips($value)."</option>";
						}
						$sisu .= "</select>";
				}
				else {
					$sisu .= "<input type='checkbox' name='".$columns[0]."_equals' value='checked' ".$n3." /></td><td><input type='text' name='".$columns[0]."' value='".$n."'>".pretty_filetype($tyyp);
				}
				$sisu .= "</td></tr>";
				}
			} //genereeriti html vorm
		}
		genereeriOtsimiseVorm($columns1);
		if (in_array($xtabel, $publicTables)) {
			$sisu .= "<tr><td colspan='3'><br /></td></tr>";
			$sisu .= "<tr><td colspan='3'><b>".$lang["andmed_osaleja_kohta"]."</b></td></tr>";
			$columns2[] = array("rolli_nimetus");
			genereeriOtsimiseVorm($columns2);
		}
		$sisu .=<<<SISU
<tr><td></td><td></td><td><input type="submit" name="$xtabel" value="$lang[otsi]"></td></tr>
</table>
<input type="hidden" name="tabel" value="$xtabel">
</form>
SISU;
	} //tabel oli olemas
} //taheti saada otsimise vorm
else { //tahetakse näha otsimise avalehte
$pealkiri1 = $lang["otsi"];
if (isset($_SESSION["otsing"])) {
	$viimaneOtsing = "<tr><td><a href='".$_SESSION["otsing"]."'>".$lang["viimane_otsing"]."</a></td></tr>";
}
else { $viimaneOtsing = ""; }
	$sisu =<<<SISU
<form action="" method="GET">
	<table width="100%">
	$viimaneOtsing
	<tr><td><a href="abi.php?t=otsi" target="_blank">$lang[otsinguabi]</a></td></tr>
	<tr><td><a href="$minuLemmikudLink">$lang[minu_lemmikud]</a></td></tr>
	<tr><td>
	$lang[otsi_andmebaasist]<br />
	$lang[tapne_vaste] <input type="checkbox" name="equals" value="jah" /><br />
	<input type="text" name="mida"> <input type="submit" name="otsi_andmebaasist" value="$lang[otsi]">
	</td></tr>
	<tr><td>$lang[otsi_paremalt]</td></tr>
	</table>
</form>
SISU;
} //taheti näha otsimise avalehte
$gentime = gentime();
include("kujundus.php");
?>
